﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Ko2.DB
{

    public partial class OneC : OneCBase
    {

        public Exception GetCheckPoints(ref DataTable tbl)
        {
            Exception ret = null;
            Ko2.DB.MySql mysql = new MySql();
            string ConnStr = _env.GetAppSetting("RetailStatConnect");
            ConnStr = ConnStr.Replace(@"RetailStatPwd", @"JKHDF83wdu&EY");
            string sql =
@"SELECT ID, Name, Name as NameAsAxapta FROM Shops ORDER BY Name";
            ret = GetTable(ref tbl, ConnStr, sql);
            if (ret == null)
                mysql.VerifyShopsNames(ref tbl, "NameAsAxapta");
            return ret;
        }

        public Exception GetCheckPointsData(ref DataTable tbl, string Point, int Year, int Month)
        {
            string ConnStr = _env.GetAppSetting("RetailStatConnect");
            ConnStr = ConnStr.Replace(@"RetailStatPwd", @"JKHDF83wdu&EY");
            string sql =
"SELECT DAY(t.DateCreated) as Day,SUM(t.RealIn) as RealIn FROM Shops s "
+ "INNER JOIN Entries e ON e.ShopID=s.ID INNER JOIN Sensors n ON n.EntryID=e.ID "
+ "INNER JOIN Traffic t ON t.SensorID=n.ID WHERE "
+ "YEAR(t.DateCreated)=" + Year.ToString() + " AND MONTH(t.DateCreated)=" + Month.ToString()
+ " AND s.Name='" + Point + "'"
+ " GROUP BY s.[Name], DAY(t.DateCreated) ";

            return GetTable(ref tbl, ConnStr, sql);
        }

        public Exception GetReturnsKontragent(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("1CConnect");
            string sql =
@"
select DISTINCT Kontragent
from (
SELECT  RealizaciyaaaTovarovUslug._Number, CONVERT(char(10), RealizaciyaaaTovarovUslug._Date_Time, 121) AS Date, Skladj._Description AS Sklad, 
            Kontragentj._Description AS Kontragent, SUM(RealizaciyaaaTovarovUslugTovarj._Fld4689) AS Sum
FROM        [S-DB03].[OU20].[dbo].[_Document186_VT4681] AS RealizaciyaaaTovarovUslugTovarj LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Document186] AS RealizaciyaaaTovarovUslug LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference75] AS Skladj ON RealizaciyaaaTovarovUslug._Fld4667RRef = Skladj._IDRRef LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference43] AS Kontragentj ON RealizaciyaaaTovarovUslug._Fld4662RRef = Kontragentj._IDRRef ON 
                RealizaciyaaaTovarovUslugTovarj._Document186_IDRRef = RealizaciyaaaTovarovUslug._IDRRef
WHERE   (RealizaciyaaaTovarovUslug._Posted = 1) AND (Kontragentj._ParentIDRRef = 0x815e000a5e5c6b8511dc27f35e2208e9)
GROUP BY RealizaciyaaaTovarovUslug._Number, CONVERT(char(10), RealizaciyaaaTovarovUslug._Date_Time, 121), Skladj._Description, Kontragentj._Description
UNION ALL
SELECT VozvratTovarovPostavchhhiku._Number, CONVERT(char(10), VozvratTovarovPostavchhhiku._Date_Time, 121) AS Date, Skladj._Description AS Sklad, 
        Kontragentj._Description AS Kontragent, VozvratTovarovPostavchhhiku._Fld1986
FROM    [S-DB03].[OU20].[dbo].[_Document105] AS VozvratTovarovPostavchhhiku LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference75] AS Skladj ON VozvratTovarovPostavchhhiku._Fld1978RRef = Skladj._IDRRef LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference43] AS Kontragentj ON VozvratTovarovPostavchhhiku._Fld1980RRef = Kontragentj._IDRRef
WHERE     (VozvratTovarovPostavchhhiku._Posted = 1) AND (Kontragentj._ParentIDRRef = 0x815e000a5e5c6b8511dc27f35e2208e9)
GROUP BY Skladj._Description, Kontragentj._Description, VozvratTovarovPostavchhhiku._Number, CONVERT(char(10), VozvratTovarovPostavchhhiku._Date_Time, 121), 
                      VozvratTovarovPostavchhhiku._Fld1986
) as tbl
 ORDER BY Kontragent
";
            return GetTable(ref tbl, ConnStr, sql);
        }

        public Exception GetReturnsSklad(ref DataTable tbl)
        {
            string ConnStr = _env.GetAppSetting("1CConnect");
            string sql =
@"
select DISTINCT Sklad
from (
SELECT  RealizaciyaaaTovarovUslug._Number, CONVERT(char(10), RealizaciyaaaTovarovUslug._Date_Time, 121) AS Date, Skladj._Description AS Sklad, 
            Kontragentj._Description AS Kontragent, SUM(RealizaciyaaaTovarovUslugTovarj._Fld4689) AS Sum
FROM        [S-DB03].[OU20].[dbo].[_Document186_VT4681] AS RealizaciyaaaTovarovUslugTovarj LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Document186] AS RealizaciyaaaTovarovUslug LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference75] AS Skladj ON RealizaciyaaaTovarovUslug._Fld4667RRef = Skladj._IDRRef LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference43] AS Kontragentj ON RealizaciyaaaTovarovUslug._Fld4662RRef = Kontragentj._IDRRef ON 
                RealizaciyaaaTovarovUslugTovarj._Document186_IDRRef = RealizaciyaaaTovarovUslug._IDRRef
WHERE   (RealizaciyaaaTovarovUslug._Posted = 1) AND (Kontragentj._ParentIDRRef = 0x815e000a5e5c6b8511dc27f35e2208e9)
GROUP BY RealizaciyaaaTovarovUslug._Number, CONVERT(char(10), RealizaciyaaaTovarovUslug._Date_Time, 121), Skladj._Description, Kontragentj._Description
UNION ALL
SELECT VozvratTovarovPostavchhhiku._Number, CONVERT(char(10), VozvratTovarovPostavchhhiku._Date_Time, 121) AS Date, Skladj._Description AS Sklad, 
        Kontragentj._Description AS Kontragent, VozvratTovarovPostavchhhiku._Fld1986
FROM    [S-DB03].[OU20].[dbo].[_Document105] AS VozvratTovarovPostavchhhiku LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference75] AS Skladj ON VozvratTovarovPostavchhhiku._Fld1978RRef = Skladj._IDRRef LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference43] AS Kontragentj ON VozvratTovarovPostavchhhiku._Fld1980RRef = Kontragentj._IDRRef
WHERE     (VozvratTovarovPostavchhhiku._Posted = 1) AND (Kontragentj._ParentIDRRef = 0x815e000a5e5c6b8511dc27f35e2208e9)
GROUP BY Skladj._Description, Kontragentj._Description, VozvratTovarovPostavchhhiku._Number, CONVERT(char(10), VozvratTovarovPostavchhhiku._Date_Time, 121), 
                      VozvratTovarovPostavchhhiku._Fld1986
) as tbl
 ORDER BY Sklad
";
            return GetTable(ref tbl, ConnStr, sql);
        }

        public Exception GetReturns(ref DataTable tbl, DateTime DTFrom, DateTime DTTo, string Kontragent, string Sklad)
        {
            string where = "";
            StringBuilder sb = new StringBuilder();
            sb.Append(" WHERE ");

            sb.Append(" (CONVERT(smalldatetime, DATEADD(year, - 2000, CAST(Date AS Date)), 105) >= "
                + "CONVERT(DATETIME, '"
                + DTFrom.Year.ToString("0000")
                + "-" + DTFrom.Month.ToString("00") 
                + "-" + DTFrom.Day.ToString("00") 
                + " 00:00:00', 102)) ");

            sb.Append(" AND (CONVERT(smalldatetime, DATEADD(year, - 2000, CAST(Date AS Date)), 105) <= "
                + "CONVERT(DATETIME, '"
                + DTTo.Year.ToString("0000")
                + "-" + DTTo.Month.ToString("00")
                + "-" + DTTo.Day.ToString("00")
                + " 00:00:00', 102)) ");

            if (!String.IsNullOrEmpty(Kontragent))
                sb.Append(@" AND (Kontragent = N'" + Kontragent + @"') ");

            if (!String.IsNullOrEmpty(Sklad))
                sb.Append(@" AND (Sklad = N'" + Sklad + @"') ");

            where = sb.ToString();
            return GetReturns(ref tbl, where);
        }

        public Exception GetReturns(ref DataTable tbl, string where)
        {
            string ConnStr = _env.GetAppSetting("1CConnect");
            #region SQL
            string sql =
@"
select 
CONVERT(smalldatetime, DATEADD(year, - 2000, CAST(Date AS Date)), 105) AS DT
, Sklad
, Kontragent
, _Number
, Sum
from (
SELECT  RealizaciyaaaTovarovUslug._Number, CONVERT(char(10), RealizaciyaaaTovarovUslug._Date_Time, 121) AS Date, Skladj._Description AS Sklad, 
            Kontragentj._Description AS Kontragent, SUM(RealizaciyaaaTovarovUslugTovarj._Fld4689) AS Sum
FROM        [S-DB03].[OU20].[dbo].[_Document186_VT4681] AS RealizaciyaaaTovarovUslugTovarj LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Document186] AS RealizaciyaaaTovarovUslug LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference75] AS Skladj ON RealizaciyaaaTovarovUslug._Fld4667RRef = Skladj._IDRRef LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference43] AS Kontragentj ON RealizaciyaaaTovarovUslug._Fld4662RRef = Kontragentj._IDRRef ON 
                RealizaciyaaaTovarovUslugTovarj._Document186_IDRRef = RealizaciyaaaTovarovUslug._IDRRef
WHERE   (RealizaciyaaaTovarovUslug._Posted = 1) AND (Kontragentj._ParentIDRRef = 0x815e000a5e5c6b8511dc27f35e2208e9)
GROUP BY RealizaciyaaaTovarovUslug._Number, CONVERT(char(10), RealizaciyaaaTovarovUslug._Date_Time, 121), Skladj._Description, Kontragentj._Description
UNION ALL
SELECT VozvratTovarovPostavchhhiku._Number, CONVERT(char(10), VozvratTovarovPostavchhhiku._Date_Time, 121) AS Date, Skladj._Description AS Sklad, 
        Kontragentj._Description AS Kontragent, VozvratTovarovPostavchhhiku._Fld1986
FROM    [S-DB03].[OU20].[dbo].[_Document105] AS VozvratTovarovPostavchhhiku LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference75] AS Skladj ON VozvratTovarovPostavchhhiku._Fld1978RRef = Skladj._IDRRef LEFT OUTER JOIN
            [S-DB03].[OU20].[dbo].[_Reference43] AS Kontragentj ON VozvratTovarovPostavchhhiku._Fld1980RRef = Kontragentj._IDRRef
WHERE     (VozvratTovarovPostavchhhiku._Posted = 1) AND (Kontragentj._ParentIDRRef = 0x815e000a5e5c6b8511dc27f35e2208e9)
GROUP BY Skladj._Description, Kontragentj._Description, VozvratTovarovPostavchhhiku._Number, CONVERT(char(10), VozvratTovarovPostavchhhiku._Date_Time, 121), 
                      VozvratTovarovPostavchhhiku._Fld1986
) as tbl
";
            #endregion SQL
            if (!String.IsNullOrEmpty(where))
                sql += " " + where;
            return GetTable(ref tbl, ConnStr, sql);
        }

        public Exception GetDateBirth(ref string parStr, String parID1C)
        {
            Exception ret = null;
            DataTable tbl = new DataTable("tbl");
            string ConnStr = _env.GetAppSetting("1CConnect");
            #region sql
            string sqlStr =
@"
Select 
convert(char(10), f._Fld1165,121) as DnRo
, convert(char(10), Max (r._Period),121) As Period 
From _InfoRg5080 As r 
    Left Join _Reference84 As s Inner Join _Reference101 As f 
        On s._Fld993RRef = f._IDRRef On r._Fld5081RRef = s._IDRRef 
Where 
    r._Fld5096RRef = 0xa4854b8d2b756bb24cf0d8eacc60b5d6 
    And s._Fld998RRef = 0x850666574703fd8a4f18c2299ebfad6a 
    And s._Fld994 = 1 
    AND f._Code = '" + parID1C + @"' 
Group By f._Code , f._Fld1165
";
            #endregion sql
            ret = GetTable(ref tbl, ConnStr, sqlStr);
            if (ret != null)
                return ret;
            if (tbl.Rows.Count <= 0)
                return ret;
            parStr = (string)tbl.Rows[0][0];
            return ret;
        }

        public Exception GetDateArb(ref string parStr, String parID1C)
        {
            Exception ret = null;
            DataTable tbl = new DataTable("tbl");
            string ConnStr = _env.GetAppSetting("1CConnect");
            #region sql
            string sqlStr =
@"
Select 
      convert(char(10), f._Fld1165,121) as DnRo
    , convert(char(10), Max (r._Period),121) As Period 
From 
    _InfoRg5080 As r 
        Left Join _Reference84 As s Inner Join _Reference101 As f 
            On s._Fld993RRef = f._IDRRef On r._Fld5081RRef = s._IDRRef 
Where 
    r._Fld5096RRef = 0xa4854b8d2b756bb24cf0d8eacc60b5d6 
    And s._Fld998RRef = 0x850666574703fd8a4f18c2299ebfad6a 
    And s._Fld994 = 1 AND f._Code = '" + parID1C + @"' 
Group By f._Code , f._Fld1165
";
            #endregion sql
            ret = GetTable(ref tbl, ConnStr, sqlStr);
            if (ret != null)
                return ret;
            if (tbl.Rows.Count <= 0)
                return ret;
            parStr = (string)tbl.Rows[0][1];
            return ret;
        }

    }

}
